﻿CREATE TABLE [dbo].[Rulaje] (
    [CodFormula]        BIGINT          IDENTITY (1, 1) NOT NULL,
    [ContDebit]         [dbo].[TipCont] NOT NULL,
    [ContCredit]        [dbo].[TipCont] NOT NULL,
    [CodValuta]         SMALLINT        CONSTRAINT [DF_Rulaje_CodValuta] DEFAULT ((0)) NOT NULL,
    [IDDocument]        BIGINT          NOT NULL,
    [ValoareValuta]     DECIMAL (20, 2) CONSTRAINT [DF_Rulaje_ValareaValuta] DEFAULT ((0)) NOT NULL,
    [Curs]              DECIMAL (18, 4) CONSTRAINT [DF_Rulaje_Curs] DEFAULT ((1)) NOT NULL,
    [ValoareLei]        DECIMAL (20, 2) CONSTRAINT [DF_Rulaje_ValoareLei] DEFAULT ((0)) NOT NULL,
    [DataDocJust]       DATETIME        NOT NULL,
    [TipDocJust]        VARCHAR (10)    CONSTRAINT [DF_Rulaje_TipDocJust] DEFAULT ('NC') NOT NULL,
    [NumarDocJust]      DECIMAL (18)    NOT NULL,
    [ExplicatiiDocJust] VARCHAR (200)   NOT NULL,
    [NrCrt]             SMALLINT        CONSTRAINT [DF_Rulaje_NrCrt] DEFAULT ((1)) NOT NULL,
    [CodUnitate]        SMALLINT        CONSTRAINT [DF_Rulaje_CodUnitate] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_Rulaje] PRIMARY KEY CLUSTERED ([CodFormula] ASC) WITH (FILLFACTOR = 90),
    CONSTRAINT [FK_Rulaje_Conturi_Credit] FOREIGN KEY ([ContCredit], [CodUnitate]) REFERENCES [dbo].[Conturi] ([Cont], [CodUnitate]),
    CONSTRAINT [FK_Rulaje_Conturi_Debit] FOREIGN KEY ([ContDebit], [CodUnitate]) REFERENCES [dbo].[Conturi] ([Cont], [CodUnitate]),
    CONSTRAINT [FK_Rulaje_RulajeDoc] FOREIGN KEY ([IDDocument]) REFERENCES [dbo].[RulajeDoc] ([IDDocument]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_Rulaje_Unitati] FOREIGN KEY ([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
    CONSTRAINT [FK_Rulaje_Valuta] FOREIGN KEY ([CodValuta]) REFERENCES [dbo].[Valuta] ([CodValuta])
);


GO
CREATE TRIGGER [dbo].[Rulaje_T1]
ON dbo.Rulaje
FOR INSERT, UPDATE
AS
		-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @eContTerminal bit
	DECLARE @contUpdate AS TipCont
	DECLARE @mesaj AS varchar(500)
    IF UPDATE(ContDebit)
	BEGIN
		SELECT @eContTerminal = Terminal,@contUpdate = i.ContDebit FROM [Conturi] c 
		JOIN INSERTED i ON c.[Cont] = i.ContDebit AND c.[CodUnitate] = i.[CodUnitate]
		IF @eContTerminal=0 AND @contUpdate<>'N/A'
			GOTO Error1	
	END
	IF UPDATE(ContCredit)
	BEGIN		
		SELECT @eContTerminal = Terminal,@contUpdate = i.ContCredit FROM [Conturi] c 
		JOIN INSERTED i ON c.[Cont] = i.ContCredit AND c.[CodUnitate] = i.[CodUnitate]
		IF @eContTerminal=0 AND @contUpdate<>'N/A'
			GOTO Error2
	END
	GOTO Gata
	Error1:
		SET @mesaj = 'Cont neterminal in ContDebit=' + @contUpdate
		RAISERROR (@mesaj, 16, 1)
		ROLLBACK TRANSACTION
	Error2:
		SET @mesaj = 'Cont neterminal in ContCredit=' + @contUpdate
		RAISERROR (@mesaj, 16, 1)
		ROLLBACK TRANSACTION
	Gata:
GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Pozitia in nota contabila', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Rulaje', @level2type = N'COLUMN', @level2name = N'NrCrt';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Explicatii Document Justificativ', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Rulaje', @level2type = N'COLUMN', @level2name = N'ExplicatiiDocJust';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Numar Document Justificativ', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Rulaje', @level2type = N'COLUMN', @level2name = N'NumarDocJust';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Tipul documentului justificativ', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Rulaje', @level2type = N'COLUMN', @level2name = N'TipDocJust';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Data Document Justificativ', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Rulaje', @level2type = N'COLUMN', @level2name = N'DataDocJust';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Valoarea in valuta', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Rulaje', @level2type = N'COLUMN', @level2name = N'ValoareLei';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Valoarea in valua CodValuta', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Rulaje', @level2type = N'COLUMN', @level2name = N'ValoareValuta';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Codul documentului de care apartine formula', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Rulaje', @level2type = N'COLUMN', @level2name = N'IDDocument';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Codul Valutei pentru formula', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Rulaje', @level2type = N'COLUMN', @level2name = N'CodValuta';

